Exploratory Data Analysis

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np

# Any results you write to the current directory are saved as output.

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = 'all'

import math
import scipy.stats as stats

dataset_path = "autos.csv"  # Replace with the actual path to your dataset
df = pd.read_csv(dataset_path, encoding='latin-1')

# Display the first few rows of the dataset
print("Head of the dataset:")
print(df.head())

# Check the dimensions of the dataset
print("Dimensions:")
print(df.shape)

# Display the column names
print("Column names:")
print(df.columns)

# Get the summary statistics of the numerical columns
print("Summary statistics:")
df.describe()

# Check the data types of each column
print("Data types:")
print(df.dtypes) 

# Check for missing values
print("Missing values:")
df.isnull().sum()

# Calculate the percentage of null values in each column
null_percentage = (df.isnull().sum() / len(df)) * 100
print(null_percentage)

# Convert the date column to datetime format
date_column = "dateCrawled"  # Replace with the actual name of your date column
df[date_column] = pd.to_datetime(df[date_column])

# Create separate date and time columns
df['date'] = df[date_column].dt.date
df['time'] = df[date_column].dt.time

# change datatype from object to date time
df['date'] = pd.to_datetime(df['date'])
df['time'] = pd.to_datetime(df['time'],format='%H:%M:%S').dt.strftime('%H:%M:%S')

# Change the data type of column 'A' to integer
df['postalCode'] = df['postalCode'].astype(object)

# Remove null values in a specific column about 10% of the data
column_with_null = ['vehicleType','model','fuelType']  # Replace with the actual column name
df_without_nulls = df.dropna(subset= column_with_null)
df_without_nulls.isnull().sum()

# gearbox 

# Replace null values with "NA" in a specific column for no information collected
column_of_interest = 'gearbox'  # Replace with the actual column name
df_without_nulls[column_of_interest].fillna("NA", inplace=True)

# notRepairedDamage : if the car has a damage which is not repaired yet

# Replace null values with "NA" in a specific column for no information collected
column_of_interest = 'notRepairedDamage'  # Replace with the actual column name
df_without_nulls[column_of_interest].fillna("NA", inplace=True)

# Count of repaired Damage
df_without_nulls['notRepairedDamage'].value_counts()

df_without_nulls.isnull().sum()

# df_without_nulls
# # Replace invalid values with NaN
# df.loc[df["yearOfRegistration"] == 1111, "yearOfRegistration"] = np.nan

# Convert yearOfRegistration to datetime format
df_without_nulls["yearOfRegistration"] = pd.to_datetime(df_without_nulls["yearOfRegistration"], format="%Y")

# Drop irrelevant columns
# df_without_nulls = df_without_nulls['date', 'time', 'name', 'seller', 'offerType', 'price', 'abtest',
#        'vehicleType', 'yearOfRegistration', 'gearbox', 'powerPS', 'model',
#        'kilometer', 'monthOfRegistration', 'fuelType', 'brand',
#        'notRepairedDamage', 'dateCreated', 'nrOfPictures', 'postalCode' ]


df_without_nulls = df_without_nulls.drop(['dateCrawled', 'lastSeen' ], axis=1)

df_without_nulls = df_without_nulls[['date', 'time', 'name', 'seller', 'offerType', 'price', 'abtest',
       'vehicleType', 'yearOfRegistration', 'gearbox', 'powerPS', 'model',
       'kilometer', 'monthOfRegistration', 'fuelType', 'brand',
       'notRepairedDamage', 'dateCreated', 'nrOfPictures', 'postalCode' ]]
df_without_nulls

from translate import Translator
import pandas as pd

# Function to translate a list of unique words from German to English
def translate_list(unique_words):
    translator = Translator(to_lang='en', from_lang='de')
    translations = [translator.translate(word) for word in unique_words]
    return translations


# Define the unique words in German
seller = ['privat', 'gewerblich']
offerType = ['Angebot', 'Gesuch']
vehicleType = ['suv', 'kleinwagen', 'limousine', 'cabrio', 'bus', 'kombi', 'coupe', 'andere']
gearbox = ['automatik', 'manuell', 'NA']
fuelType = ['diesel', 'benzin', 'lpg', 'andere', 'hybrid', 'cng', 'elektro']
notRepairedDamage = ['NA', 'nein', 'ja']

# Translate the unique words from German to English
translated_seller = translate_list(seller)
translated_offerType = translate_list(offerType)
translated_vehicleType = translate_list(vehicleType)
translated_gearbox = translate_list(gearbox)
translated_fuelType = translate_list(fuelType)
translated_notRepairedDamage = translate_list(notRepairedDamage)

# Create the translation dictionaries
seller_dict = dict(zip(seller, translated_seller))
offerType_dict = dict(zip(offerType, translated_offerType))
vehicleType_dict = dict(zip(vehicleType, translated_vehicleType))
gearbox_dict = dict(zip(gearbox, translated_gearbox))
fuelType_dict = dict(zip(fuelType, translated_fuelType))
notRepairedDamage_dict = dict(zip(notRepairedDamage, translated_notRepairedDamage))


df = df_without_nulls

# Replace German words with English using the translation dictionaries
df['seller'] = df['seller'].map(seller_dict)
df['offerType'] = df['offerType'].map(offerType_dict)
df['vehicleType'] = df['vehicleType'].map(vehicleType_dict)
df['gearbox'] = df['gearbox'].map(gearbox_dict)
df['fuelType'] = df['fuelType'].map(fuelType_dict)
df['notRepairedDamage'] = df['notRepairedDamage'].map(notRepairedDamage_dict)

# Print the updated DataFrame
df

df["age"] = df["date"].dt.year - df["yearOfRegistration"].dt.year

# Get the summary statistics of numerical columns
print("Summary statistics:")
print(df.describe())

# Calculate the IQR
Q1 = df["price"].quantile(0.25)
Q3 = df["price"].quantile(0.75)
IQR = Q3 - Q1

# Define the lower and upper bounds
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Filter the DataFrame to exclude outliers
df_filtered = df[(df["price"] >= lower_bound) & (df["price"] <= upper_bound)]

# save filtered dataset 
filename = 'filteredData.csv'
df_filtered.to_csv(filename, header = True, index=False)

plt.figure(figsize=(8, 6))
sns.histplot(df_filtered["price"], bins=50)  # Adjust the number of bins as needed
plt.title("Distribution of Price")
plt.xlabel("Price")
plt.ylabel("Count")
plt.show()

def calculate_correlation_matrix(data):
    # Filter out non-numerical columns
    numeric_columns = data.select_dtypes(include='number')

    # Calculate the correlation matrix
    correlation_matrix = numeric_columns.corr()

    return correlation_matrix

def correlation_matrix_to_table(correlation_matrix):
    # Convert the correlation matrix to a DataFrame
    correlation_table = pd.DataFrame(correlation_matrix)

    # Reset the index and rename the columns
    correlation_table = correlation_table.reset_index().rename(columns={'index': 'Variable'})

    return correlation_table


# Calculate the correlation matrix
correlation_matrix = calculate_correlation_matrix(df_filtered)
correlation_matrix_to_table(correlation_matrix)
# correlation_matrix = data.corr()
print("Correlation Matrix:")
correlation_matrix

# Heatmap of correlations
print("Correlation Heatmap:")
sns.heatmap(correlation_matrix, annot=True, cmap="coolwarm")

# Generate scatter plot matrix
pd.plotting.scatter_matrix(df_filtered, figsize=(10, 10))

# Show the descriptive statistics table
print(stats)

# Show the generated plots
plt.show()

# correlation btn price and age of car  (No. of years )

# Create scatter plot
plt.figure(figsize=(8, 6))
sns.scatterplot(x=df_filtered["age"], y=df_filtered["price"])
plt.title("Age of Car vs. Price")
plt.xlabel("Age")
plt.ylabel("Price")
plt.show()

# Analyze the distribution of a price vs age of car 
plt.figure(figsize=(8, 6))
sns.histplot(df_filtered["age"])
plt.title("Distribution of Price")
plt.xlabel("age")
plt.ylabel("price")
plt.show()

## TOP 10 MODELS

# Group the data by model and calculate the average price for each model
model_prices = df_filtered.groupby("model")["price"].mean().reset_index()

# Sort the data by price in descending order
model_prices = model_prices.sort_values("price", ascending=False)

# Select the top 10 models
top_10_models = model_prices.head(10)

# Set the figure size
plt.figure(figsize=(12, 8))

# Create the bar chart
plt.bar(top_10_models["model"], top_10_models["price"], color="b")

# Customize the chart
plt.title("Average Price for Top 10 Models")
plt.xlabel("Model")
plt.ylabel("Price")
plt.xticks(rotation=90)  # Rotate x-axis labels for better readability

# Show the chart
plt.show()

## TOP 10 Brands

# Group the data by model and calculate the average price for each model
brand_prices = df_filtered.groupby("brand")["price"].mean().reset_index()

# Sort the data by price in descending order
brand_prices = brand_prices.sort_values("price", ascending=False)

# Select the top 10 models
top_10_brands = brand_prices.head(10)

# Set the figure size
plt.figure(figsize=(12, 8))

# Create the bar chart
plt.bar(top_10_brands["brand"], top_10_brands["price"], color="b")

# Customize the chart
plt.title("Average Price for Top 10 Brands")
plt.xlabel("Brands")
plt.ylabel("Price")
plt.xticks(rotation=90)  # Rotate x-axis labels for better readability

# Show the chart
plt.show()

import pandas as pd
import plotly.graph_objects as go

# Calculate the frequency counts for brands and models
top_10_brands = df_filtered["brand"].value_counts().nlargest(10).index
top_10_models = df_filtered["model"].value_counts().nlargest(10).index

# Filter the data for the top 10 brands
df_top_10_brands = df_filtered[df_filtered["brand"].isin(top_10_brands)]

# Calculate the frequency counts for models within each brand
top_10_models_per_brand = df_top_10_brands.groupby("brand")["model"].value_counts().groupby(level=0).nlargest(10)

# Create a hierarchical list of labels for the pie chart
labels = []
parents = []
values = []

# Add top 10 brand labels
for brand in top_10_brands:
    labels.append(brand)
    parents.append("")
    values.append(top_10_models_per_brand[brand].sum())

# Add top 10 model labels under each brand
for brand in top_10_brands:
    for model in top_10_models_per_brand[brand].index:
        labels.append(model)
        parents.append(brand)
        values.append(top_10_models_per_brand[brand][model])

# Create the pie chart figure using plotly
fig = go.Figure(go.Sunburst(
    labels=labels,
    parents=parents,
    values=values,
))

# Update the layout of the chart
fig.update_layout(
    title="Top 10 Brands and Top 10 Models by Count",
    sunburstcolorway=["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"],
    width=800,
    height=600
)

# Show the interactive pie chart
# fig.show()

df = df_filtered

import pandas as pd
import plotly.express as px

# Filter and select the top 10 brands
top_10_brands = df["brand"].value_counts().nlargest(10).index
df_top_10 = df[df["brand"].isin(top_10_brands)]

# Create a treemap chart using plotly express
fig = px.treemap(df_top_10, path=['brand', 'model'], values='price')

# Configure the layout of the chart
fig.update_layout(
    title="Price Distribution by Top 10 Brands and Models",
    margin=dict(t=30, l=0, r=0, b=0),
)

# Convert the date column to datetime format
df['date'] = pd.to_datetime(df['date'])

# Group the data by date and count the number of occurrences
date_count = df.groupby('date').size().reset_index(name='count')

# Create a line plot using plotly express
fig = px.line(date_count, x='date', y='count', title='Trend of Cars Posted')

# Configure the layout of the chart
fig.update_layout(
    xaxis=dict(title='Date'),
    yaxis=dict(title='Count'),
)

import pandas as pd
import plotly.express as px


# Create a treemap chart using plotly express
fig = px.treemap(df, path=['seller', 'offerType', 'gearbox', 'fuelType', 'notRepairedDamage'],
                 color='gearbox')

# Configure the layout of the chart
fig.update_layout(
    title="Relationship between Seller, Offer Type, Gearbox, Fuel Type, and Damage Status",
    margin=dict(t=30, l=0, r=0, b=0),
)

# Number of vehicle types 

import pandas as pd
import plotly.express as px


# Create a word cloud graph using Plotly Express
fig = px.histogram(df, x="vehicleType", title="Vehicle Type by Count", width=800, height=500)

# Configure the graph as a word cloud
fig.update_traces(marker=dict(color='skyblue'), selector=dict(type='bar'))
fig.update_layout(barmode='stack', xaxis=dict(categoryorder='total descending'))

# MOST POPULAR CAR MODELS
import pandas as pd
import matplotlib.pyplot as plt
from wordcloud import WordCloud
import plotly.graph_objects as go


# Calculate the count of each vehicle type
vehicle_type_counts = df['model'].value_counts()

# Generate the word cloud
wordcloud = WordCloud(width=800, height=400, background_color='white').generate_from_frequencies(vehicle_type_counts)

# Display the word cloud
plt.figure(figsize=(10, 5))
plt.imshow(wordcloud, interpolation='bilinear')
plt.axis('off')
plt.title('Vehicle Model Word Cloud')

# Count unique values of a categorical variable
brand_counts = df["brand"].value_counts()

# Sort the counts in descending order
brand_counts_sorted = brand_counts.sort_values(ascending=False)

# Create a bar plot of a categorical variable
plt.figure(figsize=(10, 6))
sns.countplot(x="brand", data=df, order=brand_counts_sorted.index)
plt.title("Distribution of Car Brands")
plt.xlabel("Brand")
plt.ylabel("Count")
plt.xticks(rotation=90)  # Rotate the x-axis labels by 90 degrees
plt.show()

# Count unique values of a categorical variable
model_counts = df["model"].value_counts()

# Sort the counts in descending order
model_counts_sorted = model_counts.sort_values(ascending=False)

# Select the top 20 values
model_counts_top20 = model_counts_sorted[:20]

# Create a bar plot of the top 20 categorical variable values
plt.figure(figsize=(10, 6))
sns.countplot(x="model", data=df, order=model_counts_top20.index)
plt.title("Distribution of Car models (Top 20)")
plt.xlabel("model")
plt.ylabel("Count")
plt.xticks(rotation=90)
plt.show()

# Calculate the mean price for each model
model_prices = df.groupby("model")["price"].mean()

# Sort the mean prices in descending order
model_prices_sorted = model_prices.sort_values(ascending=False)

# Select the top 20 models by price
top_20_models = model_prices_sorted[:20]

# Create a bar plot of the top 20 models by price
plt.figure(figsize=(10, 6))
sns.barplot(x=top_20_models.index, y=top_20_models.values)
plt.title("Top 20 Car Models by Price")
plt.xlabel("Model")
plt.ylabel("Average Price")
plt.xticks(rotation=90)
plt.show()

# Calculate the mean price for each brand
brand_prices = df.groupby("brand")["price"].mean()

# Sort the mean prices in descending order
brand_prices_sorted = brand_prices.sort_values(ascending=False)

# Select the top 20 brands by price
top_20_brands = brand_prices_sorted[:20]

# Create a bar plot of the top 20 brands by price
plt.figure(figsize=(10, 6))
sns.barplot(x=top_20_brands.index, y=top_20_brands.values)
plt.title("Top 20 Car brands by Price")
plt.xlabel("brand")
plt.ylabel("Average Price")
plt.xticks(rotation=90)
plt.show()

import plotly.express as px

# Count unique values of the vehicleType variable
vehicle_type_counts = df["vehicleType"].value_counts()

# Create a pie chart of the vehicleType variable
fig = px.pie(vehicle_type_counts, values=vehicle_type_counts.values, names=vehicle_type_counts.index)
fig.update_traces(textposition='inside', textinfo='percent+label')
fig.update_layout(title="Distribution of Vehicle Types by Count")

import plotly.express as px

# Calculate the average price for each vehicle type
avg_price_by_type = df.groupby("vehicleType")["price"].mean().reset_index()

# Create a pie chart of the vehicleType distribution by average price
fig = px.pie(avg_price_by_type, values="price", names="vehicleType")
fig.update_traces(textposition='inside', textinfo='percent+label')
fig.update_layout(title="Distribution of Vehicle Types by Average Price")
fig.show()

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import LabelEncoder
from sklearn.metrics import mean_squared_error
from sklearn.linear_model import LinearRegression
from sklearn.ensemble import RandomForestRegressor
from xgboost import XGBRegressor

# Convert 'date' and 'dateCreated' columns to datetime
df['date'] = pd.to_datetime(df['date'])
df['dateCreated'] = pd.to_datetime(df['dateCreated'])

# Extract relevant date components if needed (e.g., year, month)
df['yearOfRegistration'] = pd.to_datetime(df['yearOfRegistration'], format='%Y', errors='coerce').dt.year

# Drop columns that are not required for modeling
df = df.drop(['date', 'time', 'name', 'seller', 'offerType', 'abtest', 'dateCreated', 'nrOfPictures', 'postalCode'], axis=1)

# Convert categorical variables to numerical using label encoding
label_encoder = LabelEncoder()
categorical_cols = ['vehicleType', 'gearbox', 'model', 'fuelType', 'brand', 'notRepairedDamage']
for col in categorical_cols:
    df[col] = label_encoder.fit_transform(df[col].astype(str))

X = df.drop('price', axis=1)
y = df['price']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Initialize the models
linear_reg = LinearRegression()
random_forest = RandomForestRegressor()
xgb_reg = XGBRegressor()

# Train the models
linear_reg.fit(X_train, y_train)
random_forest.fit(X_train, y_train)
xgb_reg.fit(X_train, y_train)

# Make predictions
linear_reg_preds = linear_reg.predict(X_test)
random_forest_preds = random_forest.predict(X_test)
xgb_reg_preds = xgb_reg.predict(X_test)

# Calculate mean squared error (MSE)
linear_reg_mse = mean_squared_error(y_test, linear_reg_preds)
random_forest_mse = mean_squared_error(y_test, random_forest_preds)
xgb_reg_mse = mean_squared_error(y_test, xgb_reg_preds)

# Print the MSE for each model
print("Linear Regression MSE:", linear_reg_mse)
print("Random Forest MSE:", random_forest_mse)
print("XGBoost MSE:", xgb_reg_mse)

# Select the model with the lowest MSE
best_model = min(linear_reg_mse, random_forest_mse, xgb_reg_mse)
if best_model == linear_reg_mse:
    selected_model = linear_reg
    model_name = "Linear Regression"
elif best_model == random_forest_mse:
    selected_model = random_forest
    model_name = "Random Forest"
else:
    selected_model = xgb_reg
    model_name = "XGBoost"

print("Best Model:", model_name)

import pandas as pd
from sklearn.preprocessing import LabelEncoder

# Define the sample data
sample_data = {
    'vehicleType': ['limousine'],
    'yearOfRegistration': [2010],
    'gearbox': ['automatik'],
    'powerPS': [150],
    'model': ['Audi A6'],
    'kilometer': [100000],
    'monthOfRegistration': [6],
    'fuelType': ['benzin'],
    'brand': ['Audi'],
    'notRepairedDamage': ['nein'],
    'age': [11]
}

# Create a DataFrame from the sample data
sample_df = pd.DataFrame(sample_data)

# Convert categorical variables to numerical using label encoding
label_encoder = LabelEncoder()
categorical_cols = ['vehicleType', 'gearbox', 'model', 'fuelType', 'brand', 'notRepairedDamage']
for col in categorical_cols:
    sample_df[col] = label_encoder.fit_transform(sample_df[col].astype(str))

# Use the best model for price prediction
predicted_price = selected_model.predict(sample_df)

print("Predicted Price:", predicted_price[0])